use sqlx::SqlitePool;
use anyhow::Result;

pub async fn run_migrations(pool: &SqlitePool) -> Result<()> {
    // 创建用户表
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username VARCHAR(50) UNIQUE NOT NULL,
            password_hash VARCHAR(255) NOT NULL,
            salt VARCHAR(32) NOT NULL,
            home_directory VARCHAR(500) NOT NULL,
            max_storage_size BIGINT DEFAULT 1073741824,
            is_active BOOLEAN DEFAULT 1,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            last_login DATETIME,
            login_count INTEGER DEFAULT 0
        )"
    )
    .execute(pool)
    .await?;

    // 创建用户权限表
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS user_permissions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            permission_type VARCHAR(20) NOT NULL CHECK (permission_type IN ('READ', 'WRITE', 'DELETE', 'ADMIN')),
            path_pattern VARCHAR(500) NOT NULL,
            granted BOOLEAN DEFAULT 1,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )"
    )
    .execute(pool)
    .await?;

    // 创建会话表
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS sessions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            session_token VARCHAR(255) UNIQUE NOT NULL,
            client_ip VARCHAR(45) NOT NULL,
            client_port INTEGER NOT NULL,
            data_port INTEGER,
            passive_port INTEGER,
            transfer_mode VARCHAR(10) DEFAULT 'PASV',
            current_directory VARCHAR(1000) DEFAULT '/',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            last_activity DATETIME DEFAULT CURRENT_TIMESTAMP,
            is_active BOOLEAN DEFAULT 1,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )"
    )
    .execute(pool)
    .await?;

    // 创建传输日志表
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS transfer_logs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            session_id INTEGER NOT NULL,
            operation VARCHAR(10) NOT NULL CHECK (operation IN ('UPLOAD', 'DOWNLOAD')),
            file_path VARCHAR(1000) NOT NULL,
            file_size BIGINT,
            bytes_transferred BIGINT DEFAULT 0,
            transfer_speed REAL,
            status VARCHAR(20) NOT NULL CHECK (status IN ('STARTED', 'IN_PROGRESS', 'SUCCESS', 'FAILED', 'CANCELLED')),
            error_message TEXT,
            started_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            completed_at DATETIME,
            FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
        )"
    )
    .execute(pool)
    .await?;

    // 创建服务器配置表
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS server_config (
            id INTEGER PRIMARY KEY,
            key VARCHAR(100) UNIQUE NOT NULL,
            value TEXT NOT NULL,
            value_type VARCHAR(20) DEFAULT 'STRING' CHECK (value_type IN ('STRING', 'INTEGER', 'BOOLEAN', 'JSON')),
            description TEXT,
            is_system BOOLEAN DEFAULT 0,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )"
    )
    .execute(pool)
    .await?;

    // 创建登录尝试表
    sqlx::query(
        "CREATE TABLE IF NOT EXISTS login_attempts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username VARCHAR(50),
            client_ip VARCHAR(45) NOT NULL,
            success BOOLEAN NOT NULL,
            attempted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            user_agent TEXT
        )"
    )
    .execute(pool)
    .await?;

    // 创建索引
    sqlx::query("CREATE INDEX IF NOT EXISTS idx_users_username ON users(username)")
        .execute(pool)
        .await?;
    
    sqlx::query("CREATE INDEX IF NOT EXISTS idx_users_active ON users(is_active)")
        .execute(pool)
        .await?;
    
    sqlx::query("CREATE INDEX IF NOT EXISTS idx_permissions_user_id ON user_permissions(user_id)")
        .execute(pool)
        .await?;
    
    sqlx::query("CREATE INDEX IF NOT EXISTS idx_permissions_type ON user_permissions(permission_type)")
        .execute(pool)
        .await?;
    
    sqlx::query("CREATE INDEX IF NOT EXISTS idx_sessions_token ON sessions(session_token)")
        .execute(pool)
        .await?;
    
    sqlx::query("CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions(user_id)")
        .execute(pool)
        .await?;
    
    sqlx::query("CREATE INDEX IF NOT EXISTS idx_sessions_active ON sessions(is_active)")
        .execute(pool)
        .await?;
    
    sqlx::query("CREATE INDEX IF NOT EXISTS idx_transfer_logs_session ON transfer_logs(session_id)")
        .execute(pool)
        .await?;
    
    sqlx::query("CREATE INDEX IF NOT EXISTS idx_transfer_logs_status ON transfer_logs(status)")
        .execute(pool)
        .await?;
    
    sqlx::query("CREATE INDEX IF NOT EXISTS idx_transfer_logs_started ON transfer_logs(started_at)")
        .execute(pool)
        .await?;
    
    sqlx::query("CREATE INDEX IF NOT EXISTS idx_config_key ON server_config(key)")
        .execute(pool)
        .await?;
    
    sqlx::query("CREATE INDEX IF NOT EXISTS idx_login_attempts_ip ON login_attempts(client_ip)")
        .execute(pool)
        .await?;
    
    sqlx::query("CREATE INDEX IF NOT EXISTS idx_login_attempts_time ON login_attempts(attempted_at)")
        .execute(pool)
        .await?;

    Ok(())
}
